<?

//Initiate Authenticate
SecurityRedirect ();
//error_reporting(E_ALL);

/*
echo "<pre>";
print_r($_POST); exit;
echo "</pre>";
*/

//initiate Vars
define("PRIMARY_KEY","jo_id");
$FileName       = "ais_transaction_jo_costing_all_appv.php";
$FileTemplate   = "ais_transaction_jo_costing_all_appv_form.html";
$SubHeaderTitle = "Job Costing All Approved";
$TableName      = "tbljoborder";
$HTMLFormName   = "JobCostingForm";
$PRIMARY_KEY    = PRIMARY_KEY;
$PRIMARY_VALUE  = GetParam($PRIMARY_KEY,"");

$MaxGrid        = "20";
$KTRASL                = GetOfficeID();
//$HTMLFormName2  = "BudgetForm";

$FormAction    = $FileName . "?" . AddParam(GetQueryString("QueryString", ""),"form","$HTMLFormName");
$FormEncType   = "application/x-www-form-urlencoded";
//$PMode         = "";

//Initiate Grid Fields;
$FormFields    = array("jo_id"=>"","jo_code"=>"","doc_ref"=>"","project_id"=>"","debtur"=>"","staff_id"=>"","jo_date"=>"date","jo_status"=>"","jo_costing_appv_status"=>"");
buildsqlfield ("$TableName", $FormFields);


//Variable declaration
$jo_id = GetParam("jo_id","");
$project_id = GetParam("project_id","");
$staffcostingid = GetParam("staff_jo_costing_id","");
$jocostingappv = GetParam("jo_costing_appv_status","");
$username = $_SESSION['AISUser'];



list($day,$month,$year) = explode("/",GetParam("jo_date",""));
$jo_date  = "$year-$month-$day";

$today = date("Y-m-d");

//End Variable declaration


//buildsqlfield ("$TableName", $FormFields);

//Leave Here Untouch
$FormCName     = BuildHTMLField($FormFields,"name");
$FormCValue    = BuildHTMLField($FormFields,"value");
$FormHName     = BuildHTMLField($FormFields,"name",1);
$FormHValue    = BuildHTMLField($FormFields,"value",1);

//************ FORM ACTION **************


if (trim(GetParam("form",""))==trim($HTMLFormName)) {
    if (GetParam("FormAction","")=="Save") {

        if (trim(GetParam("jo_id",""))=="AUTO NUMBERED BY SYSTEM")  {
            
            //$SQLJO = "UPDATE tbljoborder SET jo_costing_appv_status = '".$jocostingappv."' WHERE jo_id = $jo_id";
            //$DBConnection->dbc->query($SQLJO);

            FormSave("tbljoborder_costing", "qty_activity", array("services_id"=>"", "activities_id"=>"","qty_activity"=>"", "qty_service"=>"", "job_costing_progress"=>"", "unit"=>"", "curr"=>"", "rate"=>"number", "amount"=>"number", "base_amount"=>"number", "tax_rate"=>"number", "tax_amount"=>"number", "total_amount"=>"number", "paid_ref"=>"", "paid_to_code"=>"","verifikator"=>"", "budget_remain"=>"number", "amount_remain"=>"number", "qty_activity_remain"=>"number", "amount_remain"=>"number", "qty_activity_remain"=>"number", "tax_amount_usd"=>"number", "total_amount_usd"=>"number", "budget_ap_remain"=>"number", "jo_costing_appv_status"=>""), array("jo_id"=>$jo_id), "jo_costing_id");

            //FormSave("tbljocargo", "nama_kapal", array("jo_id"=>"","nama_kapal"=>"","cargo_type"=>"","cargo_jumlah"=>"","cargo_berat"=>"","cargo_volume"=>"","cargo_lamp"=>""),"","jo_id",$LastProject2);

            $DBConnection->InsertGrid();


            SetLogActivity($TransNo,"Create","");

        } else {
           
            $DBConnection->dbc->query("DELETE FROM tbljoborder_costing WHERE jo_id='".$jo_id."' AND (jo_costing_appv_status = 'wait_approved' OR jo_costing_appv_status = 'approved_1' OR jo_costing_appv_status = 'approved_2') ");

            //$SQLJO = "UPDATE tbljoborder SET staff_jo_costing_id = '".$staffcostingid."', jo_costing_appv_status = '".$jocostingappv."' WHERE jo_id = $jo_id";
            //$SQLJO = "UPDATE tbljoborder SET jo_costing_appv_status = '".$jocostingappv."' WHERE jo_id = $jo_id";
            //$DBConnection->dbc->query($SQLJO);

            //echo "buffer: $buffer";

            FormSave("tbljoborder_costing", "qty_activity", array("services_id"=>"","activities_id"=>"","qty_activity"=>"", "qty_service"=>"", "job_costing_progress"=>"", "unit"=>"", "curr"=>"", "rate"=>"number", "amount"=>"number", "base_amount"=>"number", "tax_rate"=>"number", "tax_amount"=>"number", "total_amount"=>"number", "paid_ref"=>"", "paid_to_code"=>"","verifikator"=>"", "budget_remain"=>"number", "amount_remain"=>"number", "qty_activity_remain"=>"number", "amount_remain"=>"number", "qty_activity_remain"=>"number", "tax_amount_usd"=>"number", "total_amount_usd"=>"number", "budget_ap_remain"=>"number", "jo_costing_appv_status"=>""), array("jo_id"=>$jo_id), "jo_costing_id");

            //FormSave("tbljocargo", "nama_kapal", array("jo_id"=>"","nama_kapal"=>"","cargo_type"=>"","cargo_jumlah"=>"","cargo_berat"=>"","cargo_volume"=>"","cargo_lamp"=>""),"","jo_id",$LastProject2);


            if($jocostingappv == 'approved_2'){
                $SQLCheck = "SELECT DISTINCT MAX(dummy_id) from tbljoborder_costing_log";
                $getid = $DBConnection->dbc->get_var($SQLCheck);

                if ($getid == ""){
                    $getid = 0;
                }

                $getid = $getid + 1;

                FormSave("tbljoborder_costing_log", "qty_activity", array("services_id"=>"","activities_id"=>"","qty_activity"=>"", "qty_service"=>"", "job_costing_progress"=>"", "unit"=>"", "curr"=>"", "rate"=>"number", "amount"=>"number", "base_amount"=>"number", "tax_rate"=>"number", "tax_amount"=>"number", "total_amount"=>"number", "paid_ref"=>"", "paid_to_code"=>"", "budget_remain"=>"number", "amount_remain"=>"number", "qty_activity_remain"=>"number", "tax_amount_usd"=>"number", "total_amount_usd"=>"number"), array("jo_id"=>$jo_id, "jo_costing_appv_status"=>"approved_2", "dummy_id"=>$getid, "date_appv" => $today, "jo_costing_appv_status"=>""), "jo_costing_id_log");
            }


            $DBConnection->InsertGrid();

            //$DBConnection->dbc->query("UPDATE ".$TableName." SET doc_ref='".GetParam("doc_ref","")."',project_id='".GetParam("project_id","")."',debtur='".GetParam("debtur","")."',staff_id='".GetParam("staff_id","")."',jo_date='".$jo_date."' WHERE jo_id='".$jo_id."'");
            SetLogActivity($TransNo,"Update","");
        }

                //CheckAndUpdateCache ($TransNo);

        header("Location: $FileName?PageMode=Form&jo_id=$jo_id");
        exit;
    }
    if (GetParam("FormAction","")=="Delete") {
//      print "Deleted"."<br>\n";
                $SQL = "DELETE FROM tbljoborder WHERE jo_id='".GetParam("jo_id","")."'";
                $DBConnection->dbc->query($SQL);
                $SQL = "DELETE FROM tbljoactivity WHERE jo_id='".GetParam("jo_id","")."'";
                $DBConnection->dbc->query($SQL);
                SetLogActivity($TransNo,"Delete","");
        header("Location: $FileName?PageMode=Form");
        exit;
    }
}



//Field Verification
$Verification  = "False";
$SessionVar    = md5(microtime());
//$SessionVar    = "Stuck";



$VerifyFields = array();

VerifyHTML ($HTMLFormName,$VerifyFields,$SessionVar);


$InitFunction = "Masking();Masking2();";

$VerifyGrids = array();
$Masking1 = BuildMasking ($HTMLFormName,MutatorFields($FormFields,($MaxGrid+$Count)));
$Masking2 = BuildMasking ($HTMLFormName,array("plbt_idr"=>"number", "rate"=>"number", "amount"=>"number", "base_amount"=>"number", "total_amount"=>"number"),"aaaa");
$JSMasks  = $Masking1.$Masking2;
Init($SessionVar,$JSMasks);
FormGridHTML ($HTMLFormName,$VerifyGrids,$Count,$MaxGrid,$SessionVar);



//************ SET FORM MODE ***************
$USER_CANPRINT_TAX="0";

if (GetParam(PRIMARY_KEY,"")) {
        $FormMode = "Edit";
        $USER_CANPRINT="1";
        $SQL ="SELECT vat FROM ais_memorial WHERE nobkt='".GetParam(PRIMARY_KEY,"")."' AND urut=999 ";
        if (strlen($DBConnection->dbc->get_var($SQL))) $USER_CANPRINT_TAX="1";
} else {
        $FormMode = "Add";
        $USER_CANPRINT="0";
}
//******* DATABASE GRID QUERY ***********

$SQL = "SELECT ".BuildFields($FormFields)." FROM `$TableName`";
if (GetParam(PRIMARY_KEY,"") && $FormMode="Edit") {
    $SQL      = $SQL."WHERE ".PRIMARY_KEY." ='".GetParam(PRIMARY_KEY,"")."'";
    $SQL     .= " AND ktrasl='".GetOfficeID()."'";
    $SQLGrid  = $SQL." ORDER BY urut";
}

//******* SHOW FORM CONTENT *********
$TBS = new clsTinyButStrong ;
$TBS->LoadTemplate($MOD_TEMPLATE_DIR."/".$FileTemplate);

//********************************* HTML Header ******************************************

//Select Currency Table
$SQLCurr = "SELECT * FROM tblcurrency";
//Select Department:
$SQLDept = "SELECT * FROM tbldept";
//Select Receipt:
$SQLReceipt = "SELECT * FROM tblreceipt";
//Select Bank:
$SQLBank = "SELECT * FROM tblbank ORDER BY banknam";
//Select Kantor
$SQLKtr  = "SELECT * FROM tblkantor ORDER BY ktrasl";
//$SQLLocation = "SELECT * FROM $DBCoins.tblsubdepo ORDER BY sdname ";

$SQLUnit  = "SELECT  * FROM tblunit ORDER BY id";

$SQLPaymentStatus  = "SELECT  * FROM tbljopayment_status ORDER BY payment_status_id";

$SQLTaxRate  = "SELECT  * FROM tbltaxrate where tax_group='cost' ORDER BY tax_id";

$SQLCargoType  = "SELECT  * FROM tblcargotype ORDER BY cargo_id ASC";

$SQLGetKursTax = "SELECT * FROM ais_kurspjk WHERE curr = 'USD' ORDER BY tglbkt DESC";
$ResultGetKursTax  = $DBConnection->dbc->get_row($SQLGetKursTax,ARRAY_A);

$kursUSDTax = $ResultGetKursTax['rate'];



//edited by Adit van Smith



//GET Kurs
$SQLGetKurs = "SELECT * FROM ais_kurssi WHERE curr = 'USD' ORDER BY tglbkt DESC";
$ResultGetKurs  = $DBConnection->dbc->get_row($SQLGetKurs,ARRAY_A);

$kursUSD = $ResultGetKurs['rate'];
//END GET Kurs



//Project Get Header

if(GetParam(PRIMARY_KEY,"") != '' && GetParam(PRIMARY_KEY,"") != 'AUTO NUMBERED BY SYSTEM')
{
   $SQLProject = "SELECT *, c.nama as staff_marketing, e.nama as staff_operasional
                    FROM `$TableName` as a
                    JOIN tblproject as b ON a.project_id = b.project_id
                    JOIN ".$DBGeneral.".tblstaff as c ON a.staff_id = c.staff_id
                    JOIN tbldebtur as d ON a.debtur = d.CUCODE
                    LEFT JOIN ".$DBGeneral.".tblstaff as e ON a.staff_jo_costing_id = e.staff_id
                    WHERE a.".PRIMARY_KEY." ='".GetParam(PRIMARY_KEY,"")."' ORDER BY a.".PRIMARY_KEY." ASC";

   $ResultProject  = $DBConnection->dbc->get_results($SQLProject,ARRAY_A);

}

else
{
   $ResultProject  = array(
      array(

         "jo_id" => "",
         "jo_code" => "",
         "doc_ref" => "",
         "project_id" => "",
         "debtur" => "",
         "staff_id" =>"",
         "jo_date" =>$today

      )
   );

   //SELECT * FROM INFORMATION_SCHEMA.Columns where TABLE_NAME = 'Foo'

}


//Project


$HeaderFields = array("jo_id"=>"","jo_code"=>"","project_id"=>"project_id","debtur"=>"debtur","staff_id"=>"staff_id","jo_date"=>"date");

//Leave Here Untouch
$FormHName     = BuildHTMLField($HeaderFields,"name",1);
$FormHValue    = BuildHTMLField($HeaderFields,"value",1);

$SQLHeader     = "SELECT ".BuildFields($HeaderFields)." FROM $TableName ";


if (GetParam(PRIMARY_KEY,"") && $FormMode="Edit") $SQLHeader .= " WHERE ".PRIMARY_KEY."='".GetParam(PRIMARY_KEY,"")."' AND ktrasl='".GetOfficeID()."' AND urut=999 ";
$TBS->MergeBlock("currformblk",$DBMysql,$SQLCurr);

//Edited by Adit van Smith
$SQLCustomer = "SELECT * FROM ".$DBGeneral.".tbldebitur ORDER BY CUNAME ASC";
//End Edited by Adit van Smith

$SQLgetProject = "SELECT * FROM tblproject ORDER BY project_name ASC";


// HTML Value
$TBS->MergeBlock("headbln",$FormHName);
$TBS->MergeBlock("headblv",$ResultProject);
$TBS->MergeBlock("dayblk",GetArrayDay());
$TBS->MergeBlock("project",$DBMysql,$SQLgetProject);

$TBS->MergeBlock("customer",$DBMysql,$SQLCustomer);
$TBS->MergeBlock("unit",$DBMysql,$SQLUnit);
$TBS->MergeBlock("paymentStatus",$DBMysql,$SQLPaymentStatus);
$TBS->MergeBlock("taxRate",$DBMysql,$SQLTaxRate);
$TBS->MergeBlock("CargoType",$DBMysql,$SQLCargoType);



//********************************* HTML GRID *********************************************
//HTML Grid

$SQLGrid = "SELECT * FROM tbljoactivity as a
            JOIN tblservices as b on a.services_id = b.services_id
            WHERE ".PRIMARY_KEY." ='".GetParam(PRIMARY_KEY,"")."' ORDER BY a.act_id";

$Result  = $DBConnection->dbc->get_results($SQLGrid,ARRAY_A);
$Result  = BuildEmptyGrid ($FormFields, $MaxGrid, $Result);


$SQLGridJobCosting =  "SELECT *, e.nama as staff_paid_to, f.nama as verifikator_name 
                    FROM tbljoborder_costing a
                    JOIN tblservices b ON a.services_id = b.services_id
                    JOIN tblactivity_setup c ON a.activities_id = c.act_nr_id
                    LEFT JOIN tbldebtur d ON a.paid_to_code = d.CUDEBTUR_FIN
                    LEFT JOIN ".$DBGeneral.".tblstaff as e ON a.paid_to_code = e.nik
					LEFT JOIN ".$DBGeneral.".tblstaff as f ON a.verifikator = f.nik
                    WHERE a.jo_id = '".$jo_id."' AND (a.jo_costing_appv_status = 'wait_approved' OR a.jo_costing_appv_status = 'approved_1' OR a.jo_costing_appv_status = 'approved_2') ORDER BY jo_costing_id ASC";

$ResultJobCosting  = $DBConnection->dbc->get_results($SQLGridJobCosting,ARRAY_A);
$ResultJobCosting  = BuildEmptyGrid ($FormFields, $MaxGrid, $ResultJobCosting);




$SQLGridCargo = "SELECT * FROM tbljocargo as a
                 WHERE a.".PRIMARY_KEY." ='".GetParam(PRIMARY_KEY,"")."' ORDER BY a.cargo_id";

$ResultCargo  = $DBConnection->dbc->get_results($SQLGridCargo,ARRAY_A);
$ResultCargo  = BuildEmptyGrid ($FormFields, $MaxGrid, $ResultCargo);



$TBS->MergeBlock("blgJobCosting",$ResultJobCosting);
$TBS->MergeBlock("blgCargo",$ResultCargo);
$TBS->MergeBlock("blg",$Result);
$TBS->Show();

//*************** ACTION FUNCTION *******************

function FormInsert() {
        global $DBConnection,$FileName;
        $DBConnection->Insert();
        header("Location: $FileName?PageMode=Form");
        exit;
}

function FormUpdate() {
        global $DBConnection;
        $DBConnection->Update();
}

function FormDelete() {
        global $DBConnection,$FileName,$TableName;
        $DBConnection->deleteGrid("$TableName","nobkt");
        header("Location: $FileName");
        exit;
}

function FormSearch () {

}

function FormCancel () {
        global $FileName;
        header("Location: ".$FileName."?PageMode=List");
        exit;
}

function FormSave ($table, $keyField, $FormFields, $SpecialCase="", $PrimaryKey, $PrimaryKeyVal="") {
        global $FileName, $MaxGrid, $DBConnection;
        $TotalData = $MaxGrid * 2;
        $NewDataStart = GetParam("NewData","0");
        $NewDataStart = 1;



        for ($a=1;$a<=$TotalData;$a++) {

            //if (!GetParam("txtCheck".$a,"")) {
            if (GetParam($keyField.$a,"")) {
                foreach ($FormFields as $key=>$formatting) {

                         //need advice
                         /*if (!GetParam($keyField.$a,"")) {
                                print GetParam($keyField.$a,"");
                                $EndOfData = 1;
                                break;
                         }*/
                         //need advice

                        if(!empty($PrimaryKeyVal))
                        {
                            $value = GetParam($key.$a,"");

                            if ($formatting=="number") $value = str_replace(",","",$value);
                            if ($formatting=="date")
                            {
                                    list($day,$month,$year) = explode("/",$value);
                                    $value = "$year-$month-$day";
                            }
                            //Inser if the key is equal primary key, then set valu = primary header
                            if($key == "$PrimaryKey")
                            {
                               $DBConnection->add_data($table,$key,$PrimaryKeyVal);
                            }

                            else
                            {
                               $DBConnection->add_data($table,$key,$value);
                            }

                        }

                        else
                        {
                            $value = GetParam($key.$a,"");

                            if ($formatting=="number") $value = str_replace(",","",$value);
                            if ($formatting=="date")
                            {
                                    list($day,$month,$year) = explode("/",$value);
                                    $value = "$year-$month-$day";
                            }

                            $DBConnection->add_data($table,$key,$value);

                        }

                }

                $rate = StrToNumber(GetParam("rate",""));
                $jmh  = StrToNumber(GetParam("jumlah".$a,""));

                // Add by Chandra (15 May 2009)
                $cash_type = GetParam("cash_type","");
                //var_dump($cash_type); exit;



                if($cash_type) $DBConnection->add_data($table,'cash_type',$cash_type);
                // end update



                /*if ($EndOfData) {
                        break;
                }*/

                if (strlen($SpecialCase)) {
                        foreach ($SpecialCase as $key => $value) {
                                //print "add_data($table,$key,$value);<br>";
                                $DBConnection->add_data($table,$key,$value);

                        }
                }

//                print "$a < $NewDataStart<br>";
                if ($a < $NewDataStart) {
                         if (strlen($PrimaryKey)) {
                            $DBConnection->UpdateGrid($PrimaryKey,GetParam($PrimaryKey.$a,""));
                          } else {
                            $DBConnection->UpdateGrid($keyField,GetParam($keyField.$a,""));
                     }
                } else {
                        $DBConnection->InsertGrid();
//                        $DBConnection->dbc->debug();
            }
            }
        }
        $DBConnection->ClearData();
}



//*************** USER FUNCTION *******************

function buildsqlfield ($table, $arrayfield) {
        global $DBConnection;
        foreach ($arrayfield as $key=>$Formatting) {
            $value = GetParam($key,"");
            if ($Formatting=="date") {
                list($day,$month,$year) = explode("/",$value);
                $value = "$year-$month-$day";
            } elseif ($Formatting=="number") $value = str_replace(",","",$value);
            $DBConnection->add_htmlfield($table,$key,$value);
        }
}


function DeleteRecord ($sequence_name,$PageSize) {
        global $DBConnection,$FileName,$TableName;
        for ($x=1;$x<=$PageSize;$x++) {
                if (GetParam($sequence_name.$x,""))        {
                        $DBConnection->deleteGrid("$TableName","memorial_id",$sequence_name.$x);
            }
        }
}

function BuildFields ($FormFields) {
                global $DBConnection;
                $str = "";
                foreach ($FormFields as $key=>$formatting) {
                        if (strlen($str)) $str .= ",$key";
                        else $str = "$key";
                }
                return $str;
}

function MutatorFields ($FormFields, $MaxGrid) {
                global $DBConnection;
                $array_fields = array();
                for ($x=0;$x<$MaxGrid;$x++) {
                        foreach ($FormFields as $key=>$formatting){
                                $a=$x+1;
                                $array_fields[$key.$a] = "$formatting";
                        }
                }
                return $array_fields;
}

function FlushCustomer ($TableName, $tmp) {
        global $DBConnection;

        if ($tmp) $TEMPORARY = "TEMPORARY";
        $DBC = new mydb(DB_USER, DB_PASSWORD, DB_NAME2, DB_HOST);

        $SQL = "SELECT kodecustomer,nama,cr_term,kdtype FROM customer ";
        if (GetParam("kdtype","")) $SQL .= " WHERE kdtype='".GetParam("kdtype","")."'";
        $MyResult = $DBC->dbc->get_results($SQL,ARRAY_N);
        if (!$MyResult) return false;
         $session_file = substr(md5(time()), 0, 8);
           $session_file = "/tmp/".$session_file.".tmp";
        BufferToFile ($MyResult,$session_file);

        $DBConnection = new mydb(DB_USER, DB_PASSWORD, DB_NAME, DB_HOST);
        $SQL = "DROP TABLE IF EXISTS `$TableName`;";
        $DBConnection->dbc->query($SQL);

        $SQL= "CREATE $TEMPORARY TABLE `$TableName` (
                          `kodecustomer` varchar(20) NOT NULL default '',
                          `nama` varchar(50) default NULL,
                          `cr_term` int(4) default NULL,
                          `kdtype` varchar(20) default NULL
                          )";
        $DBConnection->dbc->query($SQL);

           $SQL = "LOAD DATA INFILE '$session_file' INTO TABLE $TableName FIELDS TERMINATED BY '\t' LINES TERMINATED BY '\n'";
        $DBConnection->dbc->query($SQL);
        unlink($session_file);
        return true;
}

function GetLastDateReport() {
        global $DBConnection;
        list($day,$month,$year) = explode("/",GetParam("tglbkt",""));
        $current_date = "$year-$month-$day";

    $SQL = "DELETE FROM ais_piutang_sisa WHERE aging_date>='$current_date' ";
    if (GetParam("curr","")) $SQL .= " AND curr='".GetParam("curr","")."' ";
        if (GetParam("skada999","")) $SQL .= " AND fromskada = '".GetParam("skada999","")."' ";
    if (GetParam("skada999","")) $SQL .= " AND toskada = '".GetParam("skada999","")."' ";
    $DBConnection->dbc->query($SQL);
    //$DBConnection->dbc->debug();

        $SQL = "SELECT max(aging_date) FROM ais_piutang_sisa WHERE aging_date<='$current_date' ";
        if (GetParam("curr","")) $SQL .= " AND curr='".GetParam("curr","")."' ";
        if (GetParam("skada999","")) $SQL .= " AND fromskada = '".GetParam("skada999","")."' ";
        if (GetParam("skada999","")) $SQL .= " AND toskada = '".GetParam("skada999","")."' ";

        return $DBConnection->dbc->get_var($SQL);
}

function convert_code($code,$digit)
{

    $length = strlen($code);
    $tot_str = $digit - $length;
    $code_edit = "";

    for($i=0;$i < $tot_str; $i++)
    {
        $code_edit.= "0";
    }

    $code_edit.= $code;

    return $code_edit;

}


?>
